﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using LPY.LeHealth.DTO;

namespace LPY.LeHealth.DAL
{
    using ServiceStack.OrmLite;

   public class makeDAL:BaseDAL
    {
       /// <summary>
       /// 预约表添加
       /// </summary>
       /// <param name="s"></param>
       /// <returns></returns>
      public int Addmake(subscrib s,int id)
       {
           int result = 0;
           using (SqlConnection conn = new SqlConnection("Data Source=DESKTOP-4O5PKGG\\SQLEXPRESS;Initial Catalog=szy1021;Integrated Security=True"))
          {
              conn.Open();
              StringBuilder str = new StringBuilder();
              str.AppendLine("insert into t_subscrib(Cid,Phyname,PhyDate,PhyPhone,Phycard,Physex,marital,Phyprice,makePhone,remark,uid) values ");
              str.AppendLine(" ("+s.Cid+",'" + s.Phyname + "','" + s.PhyDate + "','" + s.PhyPhone + "','" + s.Phycard + "','" + s.Physex + "','" + s.marital + "','" + s.Phyprice + "','" + s.makePhone + "','" + s.remark + "' ,"+id+")");
              SqlCommand comm = new SqlCommand(str.ToString(),conn);
              result = comm.ExecuteNonQuery();
              
          }
          return result;
       }


      /// <summary>
      /// 新增联系人
      /// </summary>
      /// <param name="s"></param>
      /// <returns></returns>
      //public int Addmake(subscrib s)
      //{
      //    int result = 0;
      //    using (SqlConnection conn = new SqlConnection("Data Source=DESKTOP-4O5PKGG\\SQLEXPRESS;Initial Catalog=szy1021;Integrated Security=True"))
      //    {
      //        conn.Open();
      //        StringBuilder str = new StringBuilder();
      //        str.AppendLine("insert into t_subscrib(Cid,Phyname,PhyDate,PhyPhone,Phycard,Physex,marital,Phyprice,makePhone,remark) values ");
      //        str.AppendLine(" (" + s.Cid + ",'" + s.Phyname + "','" + s.PhyDate + "','" + s.PhyPhone + "','" + s.Phycard + "','" + s.Physex + "','" + s.marital + "','" + s.Phyprice + "','" + s.makePhone + "','" + s.remark + "' )");
      //        SqlCommand comm = new SqlCommand(str.ToString(), conn);
      //        result = comm.ExecuteNonQuery();

      //    }
      //    return result;
      //}
      /// <summary>
      /// 注册
      /// </summary>
      /// <param name="s"></param>
      /// <returns></returns>
      public int register(UserDTO u)
      {
          int result = 0;
          using (SqlConnection conn = new SqlConnection("Data Source=DESKTOP-4O5PKGG\\SQLEXPRESS;Initial Catalog=szy1021;Integrated Security=True"))
          {
              conn.Open();
              StringBuilder str = new StringBuilder();
              str.Append("insert into t_userdto(Name,Password,PhoneNumber,RoleId,PrivileteId,Gender) values('" + u.Name + "','" + u.Password + "','" + u.PhoneNumber + "','1','1','true')");
              SqlCommand comm = new SqlCommand(str.ToString(), conn);
              result = comm.ExecuteNonQuery();

          }
          return result;
      }

       /// <summary>
       /// 显示常用客户信息
       /// </summary>
       /// <returns></returns>
       public List<subscrib> show(int id)
      {
          using (SqlConnection conn = new SqlConnection("Data Source=DESKTOP-4O5PKGG\\SQLEXPRESS;Initial Catalog=szy1021;Integrated Security=True"))
          {
              conn.Open();
              string str = "select * from t_subscrib where uid="+id;
              SqlCommand comm = new SqlCommand(str,conn);
              List<subscrib> list = new List<subscrib>();
              SqlDataReader ad = comm.ExecuteReader();
              while(ad.Read())
              {
                  subscrib s = new subscrib();
                  s.Cid = Convert.ToInt32(ad["Cid"]);
                  s.makePhone = ad["makePhone"].ToString();
                  s.marital = ad["marital"].ToString();
                  s.Id = Convert.ToInt32(ad["Id"]);
                  s.Phycard = ad["Phycard"].ToString();
                  s.PhyDate =Convert.ToDateTime(ad["PhyDate"]);
                  s.Phyname = ad["Phyname"].ToString();
                  s.PhyPhone = ad["PhyPhone"].ToString();
                  s.Phyprice = Convert.ToDecimal(ad["Phyprice"]);
                  s.Physex = ad["Physex"].ToString();
                  s.remark = ad["remark"].ToString();
                  s.uid = Convert.ToInt32(ad["uid"]);
                  list.Add(s);
              }
              return list;
          }
      }

       /// <summary>
       /// 热门主题
       /// </summary>
       /// <returns></returns>
       public List<recommen> remen()
       {
           //using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=szy1021;Integrated Security=True"))
           //{
           //    conn.Open();
           //    string str = "select * from t_recommen";
           //    SqlCommand comm = new SqlCommand(str, conn);
           //    List<recommen> list = new List<recommen>();
               
           //    SqlDataReader ad = comm.ExecuteReader();
           //    while (ad.Read())
           //    {
           //        recommen s = new recommen();
           //        s.Id = Convert.ToInt32(ad["Id"]);
           //        s.name = ad["name"].ToString();
           //        s.url = ad["url"].ToString();
           //        s.img = ad["img"].ToString();
           //        list.Add(s);
           //    }
           //    return list;
           //}
           using (var cnn=DbConnection)
               {
               var sql = cnn.From<recommen>();
               return cnn.Select(sql);
           }
       }
    }
}
